Home Explore Help
Register Sign In
J1X
/
huginn
Watch 2
Star 0
Fork 0

Browse Source
Set charset/collation properly for each text column if using MySQL.

With this change, Huginn is able to store up to 4-byte UTF-8 characters
in its database. This should fix #286.

Akinori MUSHA 10 years ago
parent
6d1caa4420
commit
db792cdd82
4 changed files with 191 additions and 34 deletions
Show Diff Stats
  1. 3 0
      config/initializers/ar_mysql_column_charset.rb
  2. 74 0
      db/migrate/20140813110107_set_charset_for_mysql.rb
  3. 34 34
      db/schema.rb
  4. 80 0
      lib/ar_mysql_column_charset.rb

+ 3 - 0
config/initializers/ar_mysql_column_charset.rb
View File 

@@ -0,0 +1,3 @@
1 
+ActiveSupport.on_load :active_record do
2 
+  require 'ar_mysql_column_charset'
3 
+end

+ 74 - 0
db/migrate/20140813110107_set_charset_for_mysql.rb
View File 

@@ -0,0 +1,74 @@
1 
+class SetCharsetForMysql < ActiveRecord::Migration
2 
+  def all_models
3 
+    @all_models ||= [
4 
+      Agent,
5 
+      AgentLog,
6 
+      Contact,
7 
+      Event,
8 
+      Link,
9 
+      Scenario,
10 
+      ScenarioMembership,
11 
+      User,
12 
+      UserCredential,
13 
+      Delayed::Job,
14 
+    ]
15 
+  end
16 
+
17 
+  def change
18 
+    conn = ActiveRecord::Base.connection
19 
+
20 
+    # This is migration is for MySQL only.
21 
+    return unless conn.is_a?(ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter)
22 
+
23 
+    reversible do |dir|
24 
+      dir.up do
25 
+        all_models.each { |model|
26 
+          table_name = model.table_name
27 
+
28 
+          # `contacts` may not exist
29 
+          next unless connection.table_exists? table_name
30 
+
31 
+          model.columns.each { |column|
32 
+            name = column.name
33 
+            type = column.type
34 
+            limit = column.limit
35 
+            options = {
36 
+              limit: limit,
37 
+              null: column.null,
38 
+              default: column.default,
39 
+            }
40 
+
41 
+            case type
42 
+            when :string, :text
43 
+              options.update(charset: 'utf8', collation: 'utf8_general_ci')
44 
+              case name
45 
+              when 'username'
46 
+                options.update(limit: 767 / 4, charset: 'utf8mb4', collation: 'utf8mb4_general_ci')
47 
+              when 'message', 'options', 'name', 'memory',
48 
+                   'handler', 'last_error', 'payload', 'description'
49 
+                options.update(charset: 'utf8mb4', collation: 'utf8mb4_bin')
50 
+              when 'type', 'schedule', 'mode', 'email',
51 
+                   'invitation_code', 'reset_password_token'
52 
+                options.update(collation: 'utf8_bin')
53 
+              when 'guid', 'encrypted_password'
54 
+                options.update(charset: 'ascii', collation: 'ascii_bin')
55 
+              end
56 
+            else
57 
+              next
58 
+            end
59 
+
60 
+            change_column table_name, name, type, options
61 
+          }
62 
+
63 
+          execute 'ALTER TABLE %s CHARACTER SET utf8 COLLATE utf8_general_ci' % table_name
64 
+        }
65 
+
66 
+        execute 'ALTER DATABASE %s CHARACTER SET utf8 COLLATE utf8_general_ci' % conn.current_database
67 
+      end
68 
+
69 
+      dir.down do
70 
+        # Do nada; no use to go back
71 
+      end
72 
+    end
73 
+  end
74 
+end

+ 34 - 34
db/schema.rb
View File 

@@ -11,14 +11,14 @@
11 11 
 #
12 12 
 # It's strongly recommended that you check this file into your version control system.
13 13
14 
-ActiveRecord::Schema.define(version: 20140723110551) do
14 
+ActiveRecord::Schema.define(version: 20140813110107) do
15 15
16 16 
   # These are extensions that must be enabled in order to support this database
17 17 
   enable_extension "plpgsql"
18 18
19 19 
   create_table "agent_logs", force: true do |t|
20 20 
     t.integer  "agent_id",                      null: false
21 
-    t.text     "message",                       null: false
21 
+    t.text     "message",           limit: 16777215,             null: false, charset: "utf8mb4", collation: "utf8mb4_bin"
22 22 
     t.integer  "level",             default: 3, null: false
23 23 
     t.integer  "inbound_event_id"
24 24 
     t.integer  "outbound_event_id"
 
@@ -28,17 +28,17 @@ ActiveRecord::Schema.define(version: 20140723110551) do
28 28
29 29 
   create_table "agents", force: true do |t|
30 30 
     t.integer  "user_id"
31 
-    t.text     "options"
32 
-    t.string   "type"
33 
-    t.string   "name"
34 
-    t.string   "schedule"
31 
+    t.text     "options",               limit: 16777215,                                charset: "utf8mb4", collation: "utf8mb4_bin"
32 
+    t.string   "type",                                                                                      collation: "utf8_bin"
33 
+    t.string   "name",                                                                  charset: "utf8mb4", collation: "utf8mb4_bin"
34 
+    t.string   "schedule",                                                                                  collation: "utf8_bin"
35 35 
     t.integer  "events_count"
36 36 
     t.datetime "last_check_at"
37 37 
     t.datetime "last_receive_at"
38 38 
     t.integer  "last_checked_event_id"
39 
-    t.datetime "created_at",                            null: false
40 
-    t.datetime "updated_at",                            null: false
41 
-    t.text     "memory"
39 
+    t.datetime "created_at",                                               null: false
40 
+    t.datetime "updated_at",                                               null: false
41 
+    t.text     "memory",                limit: 2147483647,                              charset: "utf8mb4", collation: "utf8mb4_bin"
42 42 
     t.datetime "last_web_request_at"
43 43 
     t.integer  "keep_events_for",       default: 0,     null: false
44 44 
     t.datetime "last_event_at"
 
@@ -46,7 +46,7 @@ ActiveRecord::Schema.define(version: 20140723110551) do
46 46 
     t.boolean  "propagate_immediately", default: false, null: false
47 47 
     t.boolean  "disabled",              default: false, null: false
48 48 
     t.integer  "service_id"
49 
-    t.string   "guid",                                  null: false
49 
+    t.string   "guid",                                                     null: false, charset: "ascii",   collation: "ascii_bin"
50 50 
   end
51 51
52 52 
   add_index "agents", ["guid"], name: "index_agents_on_guid", using: :btree
 
@@ -55,10 +55,10 @@ ActiveRecord::Schema.define(version: 20140723110551) do
55 55 
   add_index "agents", ["user_id", "created_at"], name: "index_agents_on_user_id_and_created_at", using: :btree
56 56
57 57 
   create_table "delayed_jobs", force: true do |t|
58 
-    t.integer  "priority",   default: 0
59 
-    t.integer  "attempts",   default: 0
60 
-    t.text     "handler"
61 
-    t.text     "last_error"
58 
+    t.integer  "priority",                    default: 0
59 
+    t.integer  "attempts",                    default: 0
60 
+    t.text     "handler",    limit: 16777215,                          charset: "utf8mb4", collation: "utf8mb4_bin"
61 
+    t.text     "last_error", limit: 16777215,                          charset: "utf8mb4", collation: "utf8mb4_bin"
62 62 
     t.datetime "run_at"
63 63 
     t.datetime "locked_at"
64 64 
     t.datetime "failed_at"
 
@@ -73,11 +73,11 @@ ActiveRecord::Schema.define(version: 20140723110551) do
73 73 
   create_table "events", force: true do |t|
74 74 
     t.integer  "user_id"
75 75 
     t.integer  "agent_id"
76 
-    t.decimal  "lat",        precision: 15, scale: 10
77 
-    t.decimal  "lng",        precision: 15, scale: 10
78 
-    t.text     "payload"
79 
-    t.datetime "created_at",                           null: false
80 
-    t.datetime "updated_at",                           null: false
76 
+    t.decimal  "lat",                           precision: 15, scale: 10
77 
+    t.decimal  "lng",                           precision: 15, scale: 10
78 
+    t.text     "payload",    limit: 2147483647,                                        charset: "utf8mb4", collation: "utf8mb4_bin"
79 
+    t.datetime "created_at",                                              null: false
80 
+    t.datetime "updated_at",                                              null: false
81 81 
     t.datetime "expires_at"
82 82 
   end
83 83 
 
@@ -107,13 +107,13 @@ ActiveRecord::Schema.define(version: 20140723110551) do
107 107 
   add_index "scenario_memberships", ["scenario_id"], name: "index_scenario_memberships_on_scenario_id", using: :btree
108 108
109 109 
   create_table "scenarios", force: true do |t|
110 
-    t.string   "name",                        null: false
110 
+    t.string   "name",                        null: false, charset: "utf8mb4", collation: "utf8mb4_bin"
111 111 
     t.integer  "user_id",                     null: false
112 112 
     t.datetime "created_at"
113 113 
     t.datetime "updated_at"
114 
-    t.text     "description"
114 
+    t.text     "description",                              charset: "utf8mb4", collation: "utf8mb4_bin"
115 115 
     t.boolean  "public",      default: false, null: false
116 
-    t.string   "guid",                        null: false
116 
+    t.string   "guid",                        null: false, charset: "ascii",   collation: "ascii_bin"
117 117 
     t.string   "source_url"
118 118 
   end
119 119 
 
@@ -142,31 +142,31 @@ ActiveRecord::Schema.define(version: 20140723110551) do
142 142 
     t.text     "credential_value",                  null: false
143 143 
     t.datetime "created_at",                        null: false
144 144 
     t.datetime "updated_at",                        null: false
145 
-    t.string   "mode",             default: "text", null: false
145 
+    t.string   "mode",             default: "text", null: false, collation: "utf8_bin"
146 146 
   end
147 147
148 148 
   add_index "user_credentials", ["user_id", "credential_name"], name: "index_user_credentials_on_user_id_and_credential_name", unique: true, using: :btree
149 149
150 150 
   create_table "users", force: true do |t|
151 
-    t.string   "email",                  default: "",    null: false
152 
-    t.string   "encrypted_password",     default: "",    null: false
153 
-    t.string   "reset_password_token"
151 
+    t.string   "email",                              default: "",    null: false,                     collation: "utf8_bin"
152 
+    t.string   "encrypted_password",                 default: "",    null: false, charset: "ascii",   collation: "ascii_bin"
153 
+    t.string   "reset_password_token",                                                                collation: "utf8_bin"
154 154 
     t.datetime "reset_password_sent_at"
155 155 
     t.datetime "remember_created_at"
156 
-    t.integer  "sign_in_count",          default: 0
156 
+    t.integer  "sign_in_count",                      default: 0
157 157 
     t.datetime "current_sign_in_at"
158 158 
     t.datetime "last_sign_in_at"
159 159 
     t.string   "current_sign_in_ip"
160 160 
     t.string   "last_sign_in_ip"
161 
-    t.datetime "created_at",                             null: false
162 
-    t.datetime "updated_at",                             null: false
163 
-    t.boolean  "admin",                  default: false, null: false
164 
-    t.integer  "failed_attempts",        default: 0
161 
+    t.datetime "created_at",                                         null: false
162 
+    t.datetime "updated_at",                                         null: false
163 
+    t.boolean  "admin",                              default: false, null: false
164 
+    t.integer  "failed_attempts",                    default: 0
165 165 
     t.string   "unlock_token"
166 166 
     t.datetime "locked_at"
167 
-    t.string   "username",                               null: false
168 
-    t.string   "invitation_code",                        null: false
169 
-    t.integer  "scenario_count",         default: 0,     null: false
167 
+    t.string   "username",               limit: 191,                 null: false, charset: "utf8mb4", collation: "utf8mb4_general_ci"
168 
+    t.string   "invitation_code",                                    null: false,                     collation: "utf8_bin"
169 
+    t.integer  "scenario_count",                     default: 0,     null: false
170 170 
   end
171 171
172 172 
   add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree

+ 80 - 0
lib/ar_mysql_column_charset.rb
View File 

@@ -0,0 +1,80 @@
1 
+require 'active_record'
2 
+
3 
+module ActiveRecord::ConnectionAdapters
4 
+  class ColumnDefinition
5 
+    module CharsetSupport
6 
+      attr_accessor :charset, :collation
7 
+    end
8 
+
9 
+    prepend CharsetSupport
10 
+  end
11 
+
12 
+  class TableDefinition
13 
+    module CharsetSupport
14 
+      def new_column_definition(name, type, options)
15 
+        column = super
16 
+        column.charset   = options[:charset]
17 
+        column.collation = options[:collation]
18 
+        column
19 
+      end
20 
+    end
21 
+
22 
+    prepend CharsetSupport
23 
+  end
24 
+
25 
+  class AbstractMysqlAdapter
26 
+    module CharsetSupport
27 
+      def prepare_column_options(column, types)
28 
+        spec = super
29 
+        conn = ActiveRecord::Base.connection
30 
+        spec[:charset]   = column.charset.inspect if column.charset && column.charset != conn.charset
31 
+        spec[:collation] = column.collation.inspect if column.collation && column.collation != conn.collation
32 
+        spec
33 
+      end
34 
+
35 
+      def migration_keys
36 
+        super + [:charset, :collation]
37 
+      end
38 
+    end
39 
+
40 
+    prepend CharsetSupport
41 
+
42 
+    class SchemaCreation
43 
+      module CharsetSupport
44 
+        def column_options(o)
45 
+          column_options = super
46 
+          column_options[:charset]   = o.charset unless o.charset.nil?
47 
+          column_options[:collation] = o.collation unless o.collation.nil?
48 
+          column_options
49 
+        end
50 
+
51 
+        def add_column_options!(sql, options)
52 
+          if options[:charset]
53 
+            sql << " CHARACTER SET #{options[:charset]}"
54 
+          end
55 
+
56 
+          if options[:collation]
57 
+            sql << " COLLATE #{options[:collation]}"
58 
+          end
59 
+
60 
+          super
61 
+        end
62 
+      end
63 
+
64 
+      prepend CharsetSupport
65 
+    end
66 
+
67 
+    class Column
68 
+      module CharsetSupport
69 
+        attr_reader :charset
70 
+
71 
+        def initialize(*args)
72 
+          super
73 
+          @charset = @collation[/\A[^_]+/] unless @collation.nil?
74 
+        end
75 
+      end
76 
+
77 
+      prepend CharsetSupport
78 
+    end
79 
+  end
80 
+end if Module.method_defined?(:prepend)  # ruby >=2.0